package com.ctg.itrdc.imageconsole.control.ssh.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ctg.itrdc.imageconsole.control.ssh.model.SortedSet;
import com.ctg.itrdc.imageconsole.control.ssh.model.User;
import com.ctg.itrdc.imageconsole.control.ssh.utils.DBUtils;
import com.ctg.itrdc.imageconsole.control.ssh.utils.EncryptionUtil;

public class UserDB {

	private static Logger log = LoggerFactory.getLogger(UserDB.class);

	public static final String USERNAME = "username";
	public static final String PASSWORD = "password";

	private UserDB() {
	}

	/**
	 * returns user based on sort order defined
	 * 
	 * @param sortedSet
	 *            object that defines sort order
	 * @return sorted user list
	 */
	public static SortedSet getUserSet(SortedSet sortedSet) {
		ArrayList<User> userList = new ArrayList<>();
		String orderBy = "";
		if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
			orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
		}
		String sql = "select * from  user where status='001' " + orderBy;

		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("id"));
				user.setUsername(rs.getString(USERNAME));
				user.setPassword(rs.getString(PASSWORD));
				userList.add(user);

			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

		sortedSet.setItemList(userList);
		return sortedSet;
	}

	/**
	 * returns all admin user based on sort order defined
	 * 
	 * @param sortedSet
	 *            object that defines sort order
	 * @return sorted user list
	 */
	public static SortedSet getAdminUserSet(SortedSet sortedSet) {
		ArrayList<User> userList = new ArrayList<>();
		String orderBy = "";
		if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
			orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
		}
		String sql = "select * from  user where status='001' " + orderBy;

		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("id"));
				user.setUsername(rs.getString(USERNAME));
				user.setPassword(rs.getString(PASSWORD));
				userList.add(user);

			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

		sortedSet.setItemList(userList);
		return sortedSet;
	}

	/**
	 * returns user base on id
	 * 
	 * @param userId
	 *            user id
	 * @return user object
	 */
	public static User getUser(Long userId) {

		User user = null;
		Connection con = null;
		try {
			con = DBUtils.getConn();
			user = getUser(con, userId);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

		return user;
	}

	/**
	 * returns user base on id
	 * 
	 * @param con
	 *            DB connection
	 * @param userId
	 *            user id
	 * @return user object
	 */
	public static User getUser(Connection con, Long userId) {

		User user = null;
		try {
			PreparedStatement stmt = con.prepareStatement("select * from  user where id=?");
			stmt.setLong(1, userId);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				user = new User();
				user.setId(rs.getLong("id"));
				user.setUsername(rs.getString(USERNAME));
				user.setPassword(rs.getString(PASSWORD));
			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}

		return user;
	}

	/**
	 * inserts new user
	 *
	 * @param user
	 *            user object
	 */
	public static Long insertUser(User user) {

		Long userId = null;
		Connection con = null;
		try {
			con = DBUtils.getConn();
			userId = insertUser(con, user);
		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

		return userId;
	}

	/**
	 * inserts new user
	 * 
	 * @param con
	 *            DB connection
	 * @param user
	 *            user object
	 */
	public static Long insertUser(Connection con, User user) {
		Long userId = null;
		try {
			PreparedStatement stmt = con.prepareStatement("insert into user (username, password) values (?,?)",
					Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, user.getUsername());
			if (StringUtils.isNotEmpty(user.getPassword())) {
				stmt.setString(2, user.getPassword());
			} else {
				stmt.setString(2, null);
			}
			stmt.execute();
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs != null && rs.next()) {
				userId = rs.getLong(1);
			}
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}

		return userId;

	}

	/**
	 * updates existing user
	 * 
	 * @param user
	 *            user object
	 */
	public static void updateUserNoCredentials(User user) {
		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con.prepareStatement("update user set username=? where id=?");
			stmt.setString(1, user.getUsername());
			stmt.setLong(2, user.getId());
			stmt.execute();
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

	}

	/**
	 * updates existing user
	 * 
	 * @param user
	 *            user object
	 */
	public static void updateUserCredentials(User user) {
		Connection con = null;
		try {
			con = DBUtils.getConn();
			String salt = EncryptionUtil.generateSalt();
			PreparedStatement stmt = con.prepareStatement("update user set username=?, password=? where id=?");
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getPassword());
			stmt.setString(3, salt);
			stmt.setLong(4, user.getId());
			stmt.execute();
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

	}

	/**
	 * deletes user
	 * 
	 * @param userId
	 *            user id
	 */
	public static void disableUser(Long userId) {
		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con.prepareStatement("update user set enabled=false where id=?");
			stmt.setLong(1, userId);
			stmt.execute();
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

	}

	/**
	 * checks to see if username is unique while ignoring current user
	 *
	 * @param userId
	 *            user id
	 * @param username
	 *            username
	 * @return true false indicator
	 */
	public static boolean isUnique(Long userId, String username) {

		boolean isUnique = true;
		if (userId == null) {
			userId = -99L;
		}
		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con
					.prepareStatement("select * from user where status='001' and lower(username) like lower(?) and id != ?");
			stmt.setString(1, username);
			stmt.setLong(2, userId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				isUnique = false;
			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);
		} catch (Exception ex) {
			log.error(ex.toString(), ex);
		}
		DBUtils.closeConn(con);

		return isUnique;
	}

}
